/**
 * @author Mie Plougstrup, Anders Abildgaard, Bo Stokholm 
 * @Version 16-12-2014
 */

package dbLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.Trailer;
/**
 * @author Bo Nielsen
 * */
public class DbTrailer {
	private Connection con;
	private DbBusinessPartner dbbp;
	
	public DbTrailer(){
		dbbp = new DbBusinessPartner();
		con = DbConnection.getInstance().getDBcon();
	}
	/**
	 * Find a trailer by its trailer id.
	 * @param int id
	 * @return Trailer object
	 */
	public Trailer retrieveById(int id){
		String wClause = " trailerId = "+ id;
		return singleWhere(wClause);
	}
	/**
	 * Find trailer by trailer name.
	 * @param String attValue
	 * @return Trailer
	 */
	public Trailer retrieveByTrailerName(String attValue){
		String wClause = " trailerName like '%" + attValue + "%'";
		return singleWhere(wClause);
	}
	/**
	 * Return a list of all trailers in the Database.
	 * @return ArrayList<Trailer> list
	 */
	public ArrayList<Trailer> retrieveAllTrailers(){
		return multiWhere("");
	}
	
	/**
	 * Method for retrieving trailers not already used on dispositions.
	 * 
	 * @return List unused trailers.
	 */
	public ArrayList<Trailer> retrieveUnusedTrailers(int dispListId) {
		return multiWhere(" trailer.trailerId != 0 and not exists (select * from disposition, dispositionList where disposition.trailerId = trailer.trailerId and disposition.dispListId = dispositionList.dispListId and dispositionList.dispListId = " + dispListId + ")");
	}
	
	/**
	 * Updates a trailer in the database.
	 * @param Trailer object
	 * @return int rc
	 */
	public int update(Trailer trailer){
		int rc = -1;
		
		String query = "UPDATE trailer SET "+
		" supplierId = " + trailer.getSupplier() +
		" WHERE trailerId = " + trailer.getTrailerId();
		
		System.out.println(query);//Delete after tests
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			stmt.close();
		}catch(Exception e){
			System.out.println(e);
		}
		return (rc);
	}
	/**
	 * Insert a trailer into the database.
	 * @param Trailer object
	 * @return int rc
	 * @throws Exception
	 */
	public int insert(Trailer trailer) throws Exception{
		int nextId = DbMaxId.getMaxID("trailerId", "trailer");
		//Increment id
		nextId=nextId+1;
		
		int rc = -1;
		String query = "INSERT INTO trailer (trailerId, trailerName, supplierId) VALUES (?,?,?)";
		System.out.println(query);
		try{
			trailer.setTrailerId(nextId);
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setInt(1, trailer.getTrailerId());
			pstmt.setString(2, trailer.getTrailerName());
			pstmt.setInt(3, trailer.getSupplier().getPartnerId());
			pstmt.setQueryTimeout(5);
			
			rc = pstmt.executeUpdate();
			pstmt.close();
		}catch(Exception e){
			throw new Exception("Trailer not inserted");
		}
		return (rc);
	}
	/**
	 * Deletes a Trailer from database.
	 * @param Trailer object
	 * @return int rc
	 */
	public int delete(Trailer trailer){
		int rc = -1;
		
		String query = "DELETE FROM trailer WHERE trailerId = " + trailer.getTrailerId();
		System.out.println(query);//Delete after tests
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}catch(Exception e){
			System.out.println(e);
		}
		return (rc);
	}
	/**
	 * Executes a query statement and builds a object out
	 * the results. It then returns a single trailer object.
	 * @param String WhereClause
	 * @return Trailer object
	 */
	private Trailer singleWhere(String wClause){
		ResultSet results;
		Trailer trailObj = new Trailer();
		
		String query = buildQuery(wClause);
		System.out.println(query);//Delete after tests
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next()){
				trailObj = buildTrailer(results);
				stmt.close();
			}else
				trailObj = null;
		}catch(Exception e){
			System.out.println(e);
		}
		return trailObj;
	}
	/**
	 * Builds a list of all the trailer objects and returns the list.
	 * @param String wClause
	 * @return ArrayList<Trailer>
	 */
	private ArrayList<Trailer> multiWhere(String wClause){
		ResultSet results;
		ArrayList<Trailer> list = new ArrayList<Trailer>();
		
		String query = buildQuery(wClause);
		System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next()){
				Trailer trailObj = new Trailer();
				trailObj = buildTrailer(results);
				list.add(trailObj);
			}//End while
			
			//Close connection
			stmt.close();
		}catch(Exception e){
			System.out.println(e);//Delete after test
		}
		return list;
	}
	/**
	 * Builds a trailer from the data in a resultset.
	 * @param ResultSet results
	 * @return Trailer object
	 */
	private Trailer buildTrailer(ResultSet results){
		Trailer trailObj = new Trailer();
		try{
			trailObj.setTrailerId(results.getInt("trailerId"));
			trailObj.setTrailerName(results.getString("trailerName"));
			trailObj.setSupplier(dbbp.retrieveSupplierById(results.getInt("supplierId"))); //Set supplier and trailer together
		}catch(Exception e){
			System.out.println("Error building trailer");//Delete after tests
		}
		return trailObj;
	}
	/**
	 * Builds a SQL query that can be executed.
	 * @param String wClause
	 * @return String query
	 */
	private String buildQuery(String wClause){
		String query = "SELECT trailerId, trailerName, supplierId FROM Trailer";
		
		if(wClause.length() >0){//Change made here. 
			query=query+ " WHERE"+ wClause;
		}
		return query;
	}
}
